{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Creating DataFrames\n",
    "We will create `DataFrame` objects from other data structures in Python, by reading in a CSV file, and by querying a database.\n",
    "\n",
    "## About the Data\n",
    "In this notebook, we will be working with Earthquake data from September 18, 2018 - October 13, 2018 (obtained from the US Geological Survey (USGS) using the [USGS API](https://earthquake.usgs.gov/fdsnws/event/1/))\n",
    "\n",
    "## Imports"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import datetime\n",
    "import numpy as np\n",
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Creating a `Series`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0    0.548814\n",
       "1    0.715189\n",
       "2    0.602763\n",
       "3    0.544883\n",
       "4    0.423655\n",
       "Name: random, dtype: float64"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "np.random.seed(0) # set a seed for reproducibility\n",
    "pd.Series(np.random.rand(5), name='random')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Creating a `DataFrame` from a `Series`\n",
    "Use the `to_frame()` method:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>0</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>5.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>7.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>10.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      0\n",
       "0   0.0\n",
       "1   2.5\n",
       "2   5.0\n",
       "3   7.5\n",
       "4  10.0"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.Series(np.linspace(0, 10, num=5)).to_frame()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Creating a `DataFrame` from Python Data Structures\n",
    "### From a dictionary of list-like structures\n",
    "The dictionary values can be lists, numpy arrays, etc. as long as they have length (generators don't have length so we can't use them here):"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>random</th>\n",
       "      <th>text</th>\n",
       "      <th>truth</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>date</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2019-04-17</th>\n",
       "      <td>0.548814</td>\n",
       "      <td>hot</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2019-04-18</th>\n",
       "      <td>0.715189</td>\n",
       "      <td>warm</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2019-04-19</th>\n",
       "      <td>0.602763</td>\n",
       "      <td>cool</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2019-04-20</th>\n",
       "      <td>0.544883</td>\n",
       "      <td>cold</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2019-04-21</th>\n",
       "      <td>0.423655</td>\n",
       "      <td>None</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "              random  text  truth\n",
       "date                             \n",
       "2019-04-17  0.548814   hot  False\n",
       "2019-04-18  0.715189  warm   True\n",
       "2019-04-19  0.602763  cool   True\n",
       "2019-04-20  0.544883  cold  False\n",
       "2019-04-21  0.423655  None   True"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "np.random.seed(0) # set seed so result is reproducible\n",
    "pd.DataFrame(\n",
    "    {\n",
    "        'random': np.random.rand(5),\n",
    "        'text': ['hot', 'warm', 'cool', 'cold', None],\n",
    "        'truth': [np.random.choice([True, False]) for _ in range(5)]\n",
    "    }, \n",
    "    index=pd.date_range(\n",
    "        end=datetime.date(2019, 4, 21),\n",
    "        freq='1D',\n",
    "        periods=5, \n",
    "        name='date'\n",
    "    )\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### From a list of dictionaries"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>mag</th>\n",
       "      <th>place</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>5.2</td>\n",
       "      <td>California</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1.2</td>\n",
       "      <td>Alaska</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>0.2</td>\n",
       "      <td>California</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   mag       place\n",
       "0  5.2  California\n",
       "1  1.2      Alaska\n",
       "2  0.2  California"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.DataFrame([\n",
    "    {'mag' : 5.2, 'place' : 'California'},\n",
    "    {'mag' : 1.2, 'place' : 'Alaska'},\n",
    "    {'mag' : 0.2, 'place' : 'California'},\n",
    "])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### From a list of tuples\n",
    "This is equivalent to using `pd.DataFrame.from_records()`:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[(0, 0, 0), (1, 1, 1), (2, 4, 8), (3, 9, 27), (4, 16, 64)]"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "list_of_tuples = [(n, n**2, n**3) for n in range(5)]\n",
    "list_of_tuples"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>n</th>\n",
       "      <th>n_squared</th>\n",
       "      <th>n_cubed</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2</td>\n",
       "      <td>4</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3</td>\n",
       "      <td>9</td>\n",
       "      <td>27</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>4</td>\n",
       "      <td>16</td>\n",
       "      <td>64</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   n  n_squared  n_cubed\n",
       "0  0          0        0\n",
       "1  1          1        1\n",
       "2  2          4        8\n",
       "3  3          9       27\n",
       "4  4         16       64"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.DataFrame(\n",
    "    list_of_tuples, \n",
    "    columns=['n', 'n_squared', 'n_cubed']\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### From a NumPy array"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>n</th>\n",
       "      <th>n_squared</th>\n",
       "      <th>n_cubed</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2</td>\n",
       "      <td>4</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3</td>\n",
       "      <td>9</td>\n",
       "      <td>27</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>4</td>\n",
       "      <td>16</td>\n",
       "      <td>64</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   n  n_squared  n_cubed\n",
       "0  0          0        0\n",
       "1  1          1        1\n",
       "2  2          4        8\n",
       "3  3          9       27\n",
       "4  4         16       64"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.DataFrame(\n",
    "    np.array([\n",
    "        [0, 0, 0],\n",
    "        [1, 1, 1],\n",
    "        [2, 4, 8],\n",
    "        [3, 9, 27],\n",
    "        [4, 16, 64]\n",
    "    ]), columns=['n', 'n_squared', 'n_cubed']\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Creating a `DataFrame` by Reading in a CSV File\n",
    "\n",
    "### Finding information on the file before reading it in\n",
    "Before attempting to read in a file, we can use the command line to see important information about the file that may determine how we read it in. We can run command line code from Jupyter Notebooks (thanks to IPython) by using `!` before the code.\n",
    "\n",
    "#### Number of lines (row count)\n",
    "For example, we can find out how many lines are in the file by using the `wc` utility (word count) and counting lines in the file (`-l`). The file has 9,333 lines:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "9333 data/earthquakes.csv\n"
     ]
    }
   ],
   "source": [
    "!wc -l data/earthquakes.csv"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### File size\n",
    "We can find the file size by using `ls` to list the files in the `data` directory, and passing in the flags `-lh` to include the file size in human readable format. Then we use `grep` to find the file in question. Note that the `|` passes the result of `ls` to `grep`. `grep` is used for finding items that match patterns.\n",
    "\n",
    "This tells us the file is 3.4 MB:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "-rw-r--r-- 1 AzureAD+MolinStefanie 4096 3.4M Mar  7 13:19 earthquakes.csv\n"
     ]
    }
   ],
   "source": [
    "!ls -lh data | grep earthquakes.csv"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can even capture the result of a command and use it in our Python code:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['-rw-r--r-- 1 AzureAD+MolinStefanie 4096 3.4M Mar  7 13:19 earthquakes.csv']"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "files = !ls -lh data\n",
    "[file for file in files if 'earthquake' in file]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Examining a few rows\n",
    "We can use `head` to look at the top `n` rows of the file. With the `-n` flag, we can specify how many. This shows use that the first row of the file contains headers and that it is comma-separated (just because the file extension is `.csv` doesn't mean they have to be comma-separated values):"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "alert,cdi,code,detail,dmin,felt,gap,ids,mag,magType,mmi,net,nst,place,rms,sig,sources,status,time,title,tsunami,type,types,tz,updated,url\n",
      ",,37389218,https://earthquake.usgs.gov/fdsnws/event/1/query?eventid=ci37389218&format=geojson,0.008693,,85.0,\",ci37389218,\",1.35,ml,,ci,26.0,\"9km NE of Aguanga, CA\",0.19,28,\",ci,\",automatic,1539475168010,\"M 1.4 - 9km NE of Aguanga, CA\",0,earthquake,\",geoserve,nearby-cities,origin,phase-data,\",-480.0,1539475395144,https://earthquake.usgs.gov/earthquakes/eventpage/ci37389218\n"
     ]
    }
   ],
   "source": [
    "!head -n 2 data/earthquakes.csv"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Just like `head` gives rows from the top, `tail` gives rows from the bottom. This can help us check that there is no extraneous data on the bottom of the field, like perhaps some metadata about the fields that actually isn't part of the data set:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      ",,38063959,https://earthquake.usgs.gov/fdsnws/event/1/query?eventid=ci38063959&format=geojson,0.01865,,61.0,\",ci38063959,\",1.1,ml,,ci,27.0,\"9km NE of Aguanga, CA\",0.1,19,\",ci,\",reviewed,1537229545350,\"M 1.1 - 9km NE of Aguanga, CA\",0,earthquake,\",focal-mechanism,geoserve,nearby-cities,origin,phase-data,scitech-link,\",-480.0,1537230211640,https://earthquake.usgs.gov/earthquakes/eventpage/ci38063959\n",
      ",,38063935,https://earthquake.usgs.gov/fdsnws/event/1/query?eventid=ci38063935&format=geojson,0.01698,,39.0,\",ci38063935,\",0.66,ml,,ci,24.0,\"9km NE of Aguanga, CA\",0.1,7,\",ci,\",reviewed,1537228864470,\"M 0.7 - 9km NE of Aguanga, CA\",0,earthquake,\",focal-mechanism,geoserve,nearby-cities,origin,phase-data,scitech-link,\",-480.0,1537305830770,https://earthquake.usgs.gov/earthquakes/eventpage/ci38063935\n"
     ]
    }
   ],
   "source": [
    "!tail -n 2 data/earthquakes.csv"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Column count\n",
    "We can use `awk` to find the column count. This is a Linux utility for pattern scanning and processing. The `-F` flag allows us to specify the delimiter (comma, in this case). Then we specify what to do for each record in the file. We choose to print `NF` which is a predefined variable whose value is the number of fields in the current record. Here, we say `exit` so that we print the number of fields (columns, here) in the first row of the file, then we stop. \n",
    "\n",
    "This tells us we have 26 data columns:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "26\n"
     ]
    }
   ],
   "source": [
    "!awk -F',' '{print NF; exit}' data/earthquakes.csv"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Since we know the 1st line of the file had headers, and the file is comma-separated, we can also count the columns by using `head` to get headers and parsing them in Python:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "26"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "headers = !head -n 1 data/earthquakes.csv\n",
    "len(headers[0].split(','))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Reading in the file\n",
    "Our file is small in size, has headers in the first row, and is comma-separated, so we don't need to provide any additional arguments to read in the file with `pd.read_csv()`, but be sure to check the [documentation](http://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.read_csv.html) for possible arguments:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.read_csv('data/earthquakes.csv')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Writing a `DataFrame` to a CSV File\n",
    "Note that the index of `df` is just row numbers, so we don't want to keep it."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.to_csv('output.csv', index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Writing a `DataFrame` to a Database\n",
    "Note the `if_exists` parameter. By default it will give you an error if you try to write a table that already exists. Here, we don't care if it is overwritten. Lastly, if we are interested in appending new rows, we set that to `'append'`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [],
   "source": [
    "import sqlite3\n",
    "\n",
    "with sqlite3.connect('data/quakes.db') as connection:\n",
    "    pd.read_csv('data/tsunamis.csv').to_sql(\n",
    "        'tsunamis', connection, index=False, if_exists='replace'\n",
    "    )"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Creating a `DataFrame` by Querying a Database\n",
    "Using a SQLite database. Otherwise you need to install [SQLAlchemy](https://www.sqlalchemy.org/)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>alert</th>\n",
       "      <th>type</th>\n",
       "      <th>title</th>\n",
       "      <th>place</th>\n",
       "      <th>magType</th>\n",
       "      <th>mag</th>\n",
       "      <th>time</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>None</td>\n",
       "      <td>earthquake</td>\n",
       "      <td>M 5.0 - 165km NNW of Flying Fish Cove, Christm...</td>\n",
       "      <td>165km NNW of Flying Fish Cove, Christmas Island</td>\n",
       "      <td>mww</td>\n",
       "      <td>5.0</td>\n",
       "      <td>1539459504090</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>green</td>\n",
       "      <td>earthquake</td>\n",
       "      <td>M 6.7 - 262km NW of Ozernovskiy, Russia</td>\n",
       "      <td>262km NW of Ozernovskiy, Russia</td>\n",
       "      <td>mww</td>\n",
       "      <td>6.7</td>\n",
       "      <td>1539429023560</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>green</td>\n",
       "      <td>earthquake</td>\n",
       "      <td>M 5.6 - 128km SE of Kimbe, Papua New Guinea</td>\n",
       "      <td>128km SE of Kimbe, Papua New Guinea</td>\n",
       "      <td>mww</td>\n",
       "      <td>5.6</td>\n",
       "      <td>1539312723620</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>green</td>\n",
       "      <td>earthquake</td>\n",
       "      <td>M 6.5 - 148km S of Severo-Kuril'sk, Russia</td>\n",
       "      <td>148km S of Severo-Kuril'sk, Russia</td>\n",
       "      <td>mww</td>\n",
       "      <td>6.5</td>\n",
       "      <td>1539213362130</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>green</td>\n",
       "      <td>earthquake</td>\n",
       "      <td>M 6.2 - 94km SW of Kokopo, Papua New Guinea</td>\n",
       "      <td>94km SW of Kokopo, Papua New Guinea</td>\n",
       "      <td>mww</td>\n",
       "      <td>6.2</td>\n",
       "      <td>1539208835130</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   alert        type                                              title  \\\n",
       "0   None  earthquake  M 5.0 - 165km NNW of Flying Fish Cove, Christm...   \n",
       "1  green  earthquake            M 6.7 - 262km NW of Ozernovskiy, Russia   \n",
       "2  green  earthquake        M 5.6 - 128km SE of Kimbe, Papua New Guinea   \n",
       "3  green  earthquake         M 6.5 - 148km S of Severo-Kuril'sk, Russia   \n",
       "4  green  earthquake        M 6.2 - 94km SW of Kokopo, Papua New Guinea   \n",
       "\n",
       "                                             place magType  mag           time  \n",
       "0  165km NNW of Flying Fish Cove, Christmas Island     mww  5.0  1539459504090  \n",
       "1                  262km NW of Ozernovskiy, Russia     mww  6.7  1539429023560  \n",
       "2              128km SE of Kimbe, Papua New Guinea     mww  5.6  1539312723620  \n",
       "3               148km S of Severo-Kuril'sk, Russia     mww  6.5  1539213362130  \n",
       "4              94km SW of Kokopo, Papua New Guinea     mww  6.2  1539208835130  "
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import sqlite3\n",
    "\n",
    "with sqlite3.connect('data/quakes.db') as connection:\n",
    "    tsunamis = pd.read_sql('SELECT * FROM tsunamis', connection)\n",
    "\n",
    "tsunamis.head()"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.2"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
